package com.vd.wmt.res;

import java.io.FileInputStream;
import java.math.BigDecimal;
import java.math.MathContext;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.vd.wmt.interactive.WageListAware;
import com.vd.wmt.model.WageInfo;

public class WageExcel {
	private String fileName;
	private Workbook workBook;
	private WageListAware listAware;
	public WageExcel(String fileName,WageListAware listAware) {
		this.fileName=fileName;
		this.listAware=listAware;
	}
	public void setWageList() throws Exception{
		listAware.setWageList(this.getWageList());
	}
	public void init() throws Exception{
		synchronized (fileName) {
			if(workBook != null){
				return;
			}
			FileInputStream fps = new FileInputStream(fileName);
			if(fileName.endsWith("xls")){
				workBook = new HSSFWorkbook(fps);
			}else{
				workBook = new XSSFWorkbook(fps);
			}
		}
		
	}
	public List<WageInfo> getWageList() throws Exception{
		if(workBook == null){
			init();
		}
		List<WageInfo> wageList = new ArrayList<WageInfo>(200);
		Sheet sheet = workBook.getSheet("工资");
		if(sheet == null){
			sheet = workBook.getSheetAt(0);
		}
		int i=1;
		while (sheet.getRow(i) != null && sheet.getRow(i).getCell(0) != null){
			WageInfo wi = new WageInfo();
			Row row = sheet.getRow(i);
			String name = row.getCell(0).getStringCellValue();
			wi.setUserName(name);
			String email = row.getCell(1).getStringCellValue();
			if(!email.endsWith("@tobosoft.com.cn")){
				email+="@tobosoft.com.cn";
			}
			wi.setEmail(email);


			Cell objbasicWage = row.getCell(2);
			if(objbasicWage!=null){
				BigDecimal basicWage = new BigDecimal(String.valueOf(objbasicWage.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setBasicWage(basicWage);
			}

			Cell objbasicBonus = row.getCell(3);
			if(objbasicBonus!=null){
				BigDecimal basicBonus = new BigDecimal(String.valueOf(objbasicBonus.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setBasicBonus(basicBonus);
			}

			Cell objexpatriateSubsidy = row.getCell(4);
			if(objexpatriateSubsidy!=null){
				BigDecimal expatriateSubsidy = new BigDecimal(String.valueOf(objexpatriateSubsidy.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setExpatriateSubsidy(expatriateSubsidy);
			}

			Cell objmealSubsidy = row.getCell(5);
			if(objmealSubsidy!=null){
				BigDecimal mealSubsidy = new BigDecimal(String.valueOf(objmealSubsidy.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setMealSubsidy(mealSubsidy);
			}

			Cell objtransportSubsidy = row.getCell(6);
			if(objtransportSubsidy!=null){
				BigDecimal transportSubsidy = new BigDecimal(String.valueOf(objtransportSubsidy.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setTransportSubsidy(transportSubsidy);
			}

			Cell objattendanceDays = row.getCell(7);
			if(objattendanceDays!=null){
				BigDecimal attendanceDays = new BigDecimal(String.valueOf(objattendanceDays.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setAttendanceDays(attendanceDays);
			}

			Cell objotherSusidy = row.getCell(8);
			if(objotherSusidy!=null){
				BigDecimal otherSusidy = new BigDecimal(String.valueOf(objotherSusidy.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setOtherSusidy(otherSusidy);
			}

			Cell objotherCompensationDeduction = row.getCell(9);
			if(objotherCompensationDeduction!=null){
				BigDecimal otherCompensationDeduction = new BigDecimal(String.valueOf(objotherCompensationDeduction.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setOtherCompensationDeduction(otherCompensationDeduction);
			}

			Cell objlateCharge = row.getCell(10);
			if(objlateCharge!=null){
				BigDecimal lateCharge = new BigDecimal(String.valueOf(objlateCharge.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setLateCharge(lateCharge);
			}

			Cell objpersonIncomeTax = row.getCell(11);
			if(objpersonIncomeTax!=null){
				BigDecimal personIncomeTax = new BigDecimal(String.valueOf(objpersonIncomeTax.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setPersonIncomeTax(personIncomeTax);
			}

			Cell objsocialSecurityFund = row.getCell(12);
			if(objsocialSecurityFund!=null){
				BigDecimal socialSecurityFund = new BigDecimal(String.valueOf(objsocialSecurityFund.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setSocialSecurityFund(socialSecurityFund);
			}


			Cell objhousingFund = row.getCell(13);
			if(objhousingFund!=null){
				BigDecimal housingFund = new BigDecimal(String.valueOf(objhousingFund.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setHousingFund(housingFund);
			}

			Cell objrealWage = row.getCell(14);
			if(objrealWage!=null){
				BigDecimal realWage = new BigDecimal(String.valueOf(objrealWage.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setRealWage(realWage);
			}

			Cell objwageInBOC = row.getCell(15);
			if(objwageInBOC!=null){
				BigDecimal wageInBOC = new BigDecimal(String.valueOf(objwageInBOC.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setWageInBOC(wageInBOC);
			}

			Cell objwageInPA = row.getCell(16);
			if(objwageInPA!=null){
				BigDecimal wageInPA = new BigDecimal(String.valueOf(objwageInPA.getNumericCellValue())).setScale(2,BigDecimal.ROUND_HALF_UP);
				wi.setWageInPA(wageInPA);
			}
			wageList.add(wi);
			i++;
		}
		return wageList;
	}

}
